from flask import request, make_response, Blueprint
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.engine import Engine

engine: Engine
def make_engine(debug: bool):
    from sqlalchemy import create_engine
    engine_path = (
        "postgresql://Administrator@localhost/jwtest"
        if LOCAL_TEST or debug
        else "postgresql://jw238:123123@localhost/main"
    )
    print(f"[engine use] {engine_path}")
    engine: Engine = create_engine(engine_path)
    return engine


# psql -h localhost -p 5432 -U Administrator -d postgres
LOCAL_TEST = False
TABLE_NAME = "blog_counter"

BP_blog = Blueprint("BP_blog", __name__, url_prefix="/blog")

def init(debug: bool):
    global engine
    engine = make_engine(debug)

    if not LOCAL_TEST:
        BP_blog.add_url_rule("/visit", view_func=put, methods=["GET"])
        BP_blog.add_url_rule("/howmanyvisit", view_func=get, methods=["GET"])
        BP_blog.add_url_rule(
            "/getvisitbyname", view_func=getvisitbyname, methods=["GET"]
        )

    with engine.begin() as conn:
        sql = f"""
        SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename = '{TABLE_NAME}';
        """
        result = conn.execute(text(sql))
        tables = result.fetchall()
        if len(tables) == 0:
            sql = f"""
            CREATE TABLE {TABLE_NAME} (
                id SERIAL PRIMARY KEY,
                name TEXT NOT NULL,
                link TEXT NOT NULL,
                count INTEGER NOT NULL,
                UNIQUE (name)
            );
            CREATE UNIQUE INDEX idx_name ON {TABLE_NAME} (name);
            """
            conn.execute(text(sql))


def put():
    name = request.args.get("name")
    link = request.args.get("link")
    if not name or not link:
        return "Bad Request", 400
    try:
        _put(name, link)
        return make_response()
    except Exception as e:
        return f"An error occurred: {e}", 504


def _put(name, link):
    with engine.begin() as conn:
        sql = f"""
        INSERT INTO {TABLE_NAME} (name, link, count) VALUES (:name, :link, 1) 
        ON CONFLICT (name) DO UPDATE SET count = blog_counter.count + 1;
        """
        conn.execute(
            text(sql),
            {
                "name": name,
                "link": link,
            },
            # INSERT INTO blog_counter (name, link, count) VALUES ('asdasdasdas', 'adasdasd', 1) ON CONFLICT (name) DO UPDATE SET count = blog_counter.count + 1;
        )
        return "OK"


def get():
    try:
        # 使用 SQL 语句查询数据
        with engine.connect() as conn:
            # 查询数据
            sql = f"SELECT SUM(count) FROM {TABLE_NAME};"
            result = conn.execute(text(sql))
            if result.rowcount == 0:
                return "0", 200
            count = result.fetchone()[0]
            return str(count), 200
    except SQLAlchemyError as e:
        return f"An error occurred: {e}", 504


def getvisitbyname():
    name = request.args.get("name")
    try:
        count = _getvisitbyname(name)
        return str(count), 200
    except Exception as e:
        return f"An error occurred: {e}", 504


def _getvisitbyname(name):
    # 使用 SQL 语句查询数据
    with engine.connect() as conn:
        # 查询数据
        sql = f"SELECT count FROM {TABLE_NAME} WHERE name = :name ;"
        result = conn.execute(
            text(sql),
            {
                "name": name,
            },
        )
        if result.rowcount == 0:
            return 0
        count = result.fetchone()[0]
        return count


if __name__ == "__main__":
    LOCAL_TEST = True
    init()
    print(get()[0])
    _put("test", "httasdasdasdasadasdasda.com")
    print(_getvisitbyname("test"))
    print(get()[0])
